package drexel.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import drexel.model.Actor;
import drexel.model.Cast;
import drexel.model.DirectedBy;
import drexel.model.Director;
import drexel.model.Genre;
import drexel.model.Genres;
import drexel.model.Movie;

public class OracleDataAccess implements DataAccess {

    private Connection          conn          = null;

    private String              host          = null;
    private String              userName      = null;
    private String              password      = null;

    private final static String MOVIE_SEQ     = "movie_seq";
    private final static String ACTOR_SEQ     = "actor_seq";
    private final static String CAST_SEQ      = "cast_seq";
    private final static String GENRES_SEQ    = "genres_seq";
    private final static String DIRECTORS_SEQ = "directors_seq";

    public OracleDataAccess(String host, String userName, String password) {

        this.host = host;
        this.userName = userName;
        this.password = password;
    }

    public boolean open() {

        boolean retVal = false;

        try {

            Class.forName("oracle.jdbc.OracleDriver");

            conn = DriverManager.getConnection(this.host, this.userName, this.password);

            retVal = true;

        } catch (Exception ex) {

            ex.printStackTrace();
        }

        return retVal;
    }

    public void close() {

        if (conn != null) {

            try {
                conn.close();
            } catch (Exception ex) {
            }
        }
    }

    public boolean isDuplicate(Movie movie) {
        boolean result = false;

        try {
            int numMovies = getMovie(movie);
            int numTitlesByDirector = 0;
            int numActor = 0;
            int numGenres = 0;

            Cast cast = movie.getCast();

            for (int i = 0; i < cast.getActor().size(); i++) {
                Actor actor = (Actor)cast.getActor().get(i);
                numActor = getActor(actor);

            }

            DirectedBy directedBy = movie.getDirectedBy();
            for (int i = 0; i < directedBy.getDirector().size(); i++) {
                Director d = (Director)directedBy.getDirector().get(i);
                numTitlesByDirector = getDirectorId(d.getvalue());
            }

            Genres genres = movie.getGenres();
            for (int i = 0; i < genres.getGenre().size(); i++) {
                Genre genre = (Genre)genres.getGenre().get(i);

                if (getGenreId(genre) > 0) {
                    numGenres++;
                }
            }

            // We only want to return a duplicate if we have a hit for all of these items, otherwise we might not be
            // able to enter new data
            if (numMovies > 0 && numTitlesByDirector > 0 && numActor > 0 && numGenres > 0) {
                result = true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public boolean saveMovie(Movie movie) {

        boolean retVal = false;

        try {
            conn.setAutoCommit(false);

            System.out.println("Attempting to Save Title: " + movie.getTitle() + " - Year: " + movie.getYear() + " ...");

            // Checking if movie already exists in db by title and year

            if (!isDuplicate(movie)) {

                // Movie does not exist, so get new movie id and insert

                int movieId = insertMovie(movie);

                Cast cast = movie.getCast();

                for (int castCount = 0; castCount < cast.getActor().size(); castCount++) {

                    Actor actor = (Actor)cast.getActor().get(castCount);

                    // System.out.println(" Actor: " + actor);

                    // Check if actor already exists in actor table

                    int actorId = getActor(actor);

                    if (actorId == 0) {

                        actorId = insertActor(actor);
                    }

                    insertCast(actorId, movieId);
                }

                Genres genres = movie.getGenres();

                for (int genreCount = 0; genreCount < genres.getGenre().size(); genreCount++) {

                    Genre genre = (Genre)genres.getGenre().get(genreCount);

                    insertGenre(movieId, genre.getvalue());
                }

                DirectedBy directedBy = movie.getDirectedBy();

                for (int directorCount = 0; directorCount < directedBy.getDirector().size(); directorCount++) {

                    Director director = (Director)directedBy.getDirector().get(directorCount);

                    insertDirector(movieId, director.getvalue());
                }

                System.out.println(movie.getTitle() + " - Year: " + movie.getYear() + " Complete!");

            } else {

                System.out.println(movie.getTitle() + " - Year: " + movie.getYear() + " Already Exists");
            }

            conn.commit();

            retVal = true;

        } catch (SQLException ex) {

            try {

                // System.out.println("ERROR: Transaction is being rolled back: " + ex.toString() + ": ");

                // ex.printStackTrace();

                conn.rollback();

            } catch (SQLException excep) {

                // excep.printStackTrace();
            }
        }

        return retVal;
    }

    public MovieData getAllMoviesAndGenresByDirector(String directorName) {
        List titles = new ArrayList(0);
        List genres = new ArrayList(0);

        try {
            titles = getMovieTitlesByDirector(directorName);
            genres = getGenresByDirector(directorName);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return new MovieData(titles, genres);
    }

    public MovieData getAllMoviesAndGenresByActor(String firstName, String lastName) {

        List titles = new ArrayList(0);
        List genres = new ArrayList(0);

        try {
            titles = getTitlesByActor(firstName, lastName);
            genres = getGenresByActor(firstName, lastName);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return new MovieData(titles, genres);
    }

    private int insertCast(int actorId, int movieId) throws SQLException {

        int castId = getNextVal(CAST_SEQ);

        PreparedStatement ps = null;

        try {

            String sql = "insert into cast values (" + castId + ", " + movieId + ", " + actorId + ")";
            ps = conn.prepareStatement(sql);

            ps.executeUpdate();

        } finally {

            if (ps != null) {
                ps.close();
            }
        }

        return actorId;
    }

    private int insertDirector(int movieId, String director) throws SQLException {

        int directorId = getNextVal(DIRECTORS_SEQ);

        PreparedStatement ps = null;

        try {

            String sql = "insert into directors values (?, ?, ?)";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, directorId);
            ps.setInt(2, movieId);
            ps.setString(3, director);

            ps.executeUpdate();

        } finally {

            if (ps != null) {
                ps.close();
            }
        }

        return directorId;
    }

    private int insertGenre(int movieId, String genre) throws SQLException {

        int genreId = getNextVal(GENRES_SEQ);

        PreparedStatement ps = null;

        try {

            String sql = "insert into genres values (?,?,?)";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, genreId);
            ps.setInt(2, movieId);
            ps.setString(3, genre);

            ps.executeUpdate();

        } finally {

            if (ps != null) {
                ps.close();
            }
        }

        return genreId;
    }

    private int insertActor(Actor actor) throws SQLException {

        String firstname = actor.getFirstName();
        String lastname = actor.getLastName();
        int actorId = getNextVal(ACTOR_SEQ);

        PreparedStatement ps = null;

        try {

            String sql = "INSERT INTO actor (id, first_name, last_name) values (?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, actorId);
            ps.setString(2, firstname);
            ps.setString(3, lastname);

            ps.executeUpdate();

        } finally {

            if (ps != null) {
                ps.close();
            }
        }

        return actorId;
    }

    private int insertMovie(Movie movie) throws SQLException {

        PreparedStatement ps = null;
        String title = movie.getTitle();
        String year = movie.getYear();

        int movieId = getNextVal(MOVIE_SEQ);

        try {

            String sql = "INSERT INTO movie (id, title, year) values (?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, movieId);
            ps.setString(2, title);
            ps.setString(3, year);

            ps.executeUpdate();

        } finally {

            if (ps != null) {
                ps.close();
            }
        }

        return movieId;
    }

    private int getActor(Actor actor) throws SQLException {

        String firstname = actor.getFirstName();
        String lastname = actor.getLastName();

        int out = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            String sql = "select id from actor where first_name = ? AND last_name = ?";

            ps = conn.prepareStatement(sql);
            ps.setString(1, firstname);
            ps.setString(2, lastname);

            rs = ps.executeQuery();

            while (rs.next()) {

                out = rs.getInt(1);
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return out;
    }

    private int getMovie(Movie movie) throws SQLException {

        String title = movie.getTitle();
        String year = movie.getYear();

        int out = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            String sql = "select id from movie where title = ? AND year = ?";

            ps = conn.prepareStatement(sql);
            ps.setString(1, title);
            ps.setString(2, year);

            rs = ps.executeQuery();

            while (rs.next()) {

                out = rs.getInt(1);
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return out;
    }

    private int getDirectorId(String directorName) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;
        int id = 0;
        try {

            String sql = "select id from directors d where d.director_name = ?";

            ps = conn.prepareStatement(sql);
            ps.setString(1, directorName);

            rs = ps.executeQuery();

            while (rs.next()) {
                id = rs.getInt(1);
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return id;
    }

    private int getGenreId(Genre genre) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;
        int id = 0;
        try {

            String sql = "select id from genres g where g.genre = ?";

            ps = conn.prepareStatement(sql);
            ps.setString(1, genre.getvalue());

            rs = ps.executeQuery();

            while (rs.next()) {
                id = rs.getInt(1);
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return id;
    }

    private List getMovieTitlesByDirector(String directorName) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;
        List titles = new ArrayList();
        try {

            String sql = "select title from movie m, directors d where d.director_name = ? " + "AND m.id = d.movie_id order by title";

            ps = conn.prepareStatement(sql);
            ps.setString(1, directorName);

            rs = ps.executeQuery();

            while (rs.next()) {
                titles.add(rs.getString(1));
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return titles;
    }

    private List getGenresByDirector(String directorName) throws SQLException {

        int out = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List genres = new ArrayList();
        try {

            String sql = "select distinct g.genre from genres g, directors d, " + "movie m where d.director_name = ? AND g.movie_id = d.movie_id" + " order by g.genre";

            ps = conn.prepareStatement(sql);
            ps.setString(1, directorName);

            rs = ps.executeQuery();

            while (rs.next()) {
                genres.add(rs.getString(1));
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return genres;
    }

    private List getGenresByActor(String firstName, String lastName) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;
        List genres = new ArrayList();
        try {

            String sql = "select distinct g.genre from genres g, actor a, movie m, " + "cast c where a.first_name = ? AND a.last_name = ? AND c.actor_id = a.id "
                    + "AND c.movie_id = m.id AND g.movie_id = m.id order by g.genre";

            ps = conn.prepareStatement(sql);
            ps.setString(1, firstName);
            ps.setString(2, lastName);

            rs = ps.executeQuery();

            while (rs.next()) {
                genres.add(rs.getString(1));
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return genres;
    }

    private List getTitlesByActor(String firstName, String lastName) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;
        List titles = new ArrayList();
        try {

            String sql = "select m.title from actor a, movie m, cast c " + "where a.first_name = ? AND a.last_name = ? AND c.actor_id = a.id AND c.movie_id = m.id" + " order by m.title";

            ps = conn.prepareStatement(sql);
            ps.setString(1, firstName);
            ps.setString(2, lastName);

            rs = ps.executeQuery();

            while (rs.next()) {
                titles.add(rs.getString(1));
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return titles;
    }

    private int getNextVal(String seq) throws SQLException {

        int out = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            String sql = "select " + seq + ".nextval num from dual";

            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            while (rs.next()) {

                out = rs.getInt(1);
            }

        } finally {

            try {

                if (rs != null) {
                    rs.close();
                }

            } finally {

                try {

                    if (ps != null) {
                        ps.close();
                    }

                } finally {
                }
            }
        }

        return out;
    }
}
